import java.sql.*;
import java.util.Scanner;

public class Main {

    public static void main(String[] args) {
        try {
            SQLSys sys = new SQLSys(3306, "user_login");
            while (sys.sys()) ;
            System.out.println("欢迎下次使用");
        } catch (Exception e) {
            System.out.println("发生错误，程序终止：");
            System.out.println(e.getMessage());
        }
    }
}
class SQLSys {
    Connection connection;
    Scanner scanner;
    PreparedStatement statement;
    final String[] menuChoices = {"1.增加学生", "2.删除学生", "3.修改学生院系", "4.查询学生", "0.退出",};
    final String SQLName = "root", password = "A7btngqxA7btngqx";
    final String schema;
    SQLSys(int port, String schema)
            throws Exception {
        connection = DriverManager.getConnection(
                "jdbc:MySQL://127.0.0.1:" + port + "/" + schema
                        + "?serverTimezone=GMT&useSSL=false",
                SQLName, password);
        scanner = new Scanner(System.in);
        this.schema=schema;
    }

    void menu() {
        System.out.println("***欢迎使用学生管理系统***");
        for (String choice : menuChoices) System.out.print(choice);
        System.out.println();
    }

    boolean sys() throws Exception {
        boolean actionSuccess = false;
        menu();
        switch (scanner.nextInt()) {
            case 1:
                actionSuccess = add();
                break;
            case 2:
                actionSuccess = delete();
                break;
            case 3:
                actionSuccess = modify();
                break;
            case 4: {
                System.out.println("请输入最小年龄");
                int minAge = scanner.nextInt();
                System.out.printf("大于%d岁的学生有：", minAge);
                System.out.println();
                actionSuccess = conditionedQuery(minAge);
            }
            break;
            case 0: {
                scanner.close();
                connection.close();
                return false;
            }
        }
        return actionSuccess;
    }

    boolean add() {
        try {
            statement = connection.prepareStatement(
                    "INSERT INTO `"+schema+"`."+
                            "`sysstu` (`name`, `age`, `department`)"
                            +" VALUES (?, ?, ?);");
            System.out.println("请输入学生姓名");
            statement.setString(1, scanner.next());
            System.out.println("请输入学生年龄");
            statement.setInt(2, scanner.nextInt());
            System.out.println("请输入学生部门");
            statement.setString(3, scanner.next());
            statement.executeUpdate();
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return false;
        }
        return true;
    }

    boolean delete() {
        try {
            if (!query()) return false;
            statement = connection.prepareStatement(
                    "DELETE FROM `"+schema+"`."
                    +"`sysstu` WHERE (`name` = ?);");
            System.out.println("请输入学生姓名");
            statement.setString(1, scanner.next());
            statement.executeUpdate();
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return false;
        }
        return true;
    }

    boolean modify() {
        try {
            statement = connection.prepareStatement(
                    "UPDATE `"+schema+"`.`sysstu`"
                    +" SET `department` = ? WHERE (`name` = ?);");
            query();
            System.out.println("请输入学生姓名");
            statement.setString(2, scanner.next());
            System.out.println("请输入学生新的院系");
            statement.setString(1, scanner.next());
            statement.executeUpdate();
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return false;
        }
        return true;
    }

    boolean query() {
        ResultSet resultSet;
        String sql = "SELECT * FROM "+schema+".sysstu;";
        try {
            Statement statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery(sql);
            if (resultSet == null) {
                System.out.println("未找到数据");
                return false;
            }
			while (resultSet.next())
				System.out.println(
						resultSet.getString(1) + "\t"
								+ resultSet.getInt(2) + "\t"
								+ resultSet.getString(3)
				);
            return true;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return false;
        }
    }

    boolean conditionedQuery(int minAge) {
        try {
            statement = connection.prepareStatement("select * from "+schema+".sysstu "+"where age >= ?");
            statement.setInt(1, minAge);
            ResultSet set = statement.executeQuery();
            if (set == null) return false;
            while (set.next()) {
                System.out.println(set.getString(1) + "\t"
                        + set.getInt(2) + "\t"
                        + set.getString(3));
            }
            return true;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return false;
        }
    }
}